package com.wisemoresystem.seal.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.ne.framework.data.page.Page;
import org.ne.framework.data.util.ChkTools;
import org.springframework.stereotype.Service;

import com.wisemoresystem.seal.base.service.BaseService;
import com.wisemoresystem.seal.util.DateTools;
import com.wisemoresystem.seal.web.WebTools;

@Service
public class UseSelectService extends BaseService {

	// 用印查询
	public List<Map<String, Object>> selectUseList(String srh_reason,
			String srh_name, String srh_word, String srh_status, Page page) {
		String sql = "select t.*,tu.uname,ts.name,ts.true_code from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and t.reason like ? "
				+ " and (ts.name like ? or ts.true_code like ?) "
				+ " and (t.type_name like ? or t.word_name like ?) "
				+ " and (t.pass_status like ? or t.seal_status like ? or t.verify_status like ?) "
				+ " order by t.created desc ";
		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace("and (ts.name like ? or ts.true_code like ?)", "");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_word)) {
			sql = sql.replace("and (t.type_name like ? or t.word_name like ?)",
					"");
		} else {
			params.add("%" + srh_word + "%");
			params.add("%" + srh_word + "%");
		}
		if (ChkTools.isNull(srh_status)) {
			sql = sql
					.replace(
							"and (t.pass_status like ? or t.seal_status like ? or t.verify_status like ?)",
							"");
		} else {
			params.add("%" + srh_status + "%");
			params.add("%" + srh_status + "%");
			params.add("%" + srh_status + "%");
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	// 查询详细的使用某印章的列表
	public List<Map<String, Object>> getDoneUseObj(String srh_code, Page page) {
		String sql = "select t.*,tu.uname,ts.name,ts.true_code from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and  ts.true_code like ? "
				+ " and t.seal_status='已盖章' order by t.created desc ";

		List<Map<String, Object>> list = jc.queryForList(sql, new String[] {
				WebTools.getCurrentBankId(), srh_code }, page);
		return list;
	}

	// 用印在办
	public List<Map<String, Object>> getOndoUseList(String srh_reason,
			String srh_name, String srh_word, String srh_status, Page page) {
		String sql = "select t.*,tu.uname,ts.name,ts.true_code from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and t.reason like ? "
				+ " and (t.seal_status='待盖章' or t.seal_status is null) "
				+ " and (ts.name like ? or ts.true_code like ?) "
				+ " and (t.type_name like ? or t.word_name like ?) "
				+ " and (t.pass_status like ? or t.seal_status like ?) "
				+ " order by t.created desc ";
		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace("and (ts.name like ? or ts.true_code like ?)", "");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_word)) {
			sql = sql.replace("and (t.type_name like ? or t.word_name like ?)",
					"");
		} else {
			params.add("%" + srh_word + "%");
			params.add("%" + srh_word + "%");
		}
		if (ChkTools.isNull(srh_status)) {
			sql = sql.replace(
					"and (t.pass_status like ? or t.seal_status like ?)", "");
		} else {
			params.add("%" + srh_status + "%");
			params.add("%" + srh_status + "%");
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	public List<Map<String, Object>> getVerifyUseList(String srh_content,
			String srh_start, String srh_end, String srh_user, Page page) {
		String sql = "select t.*,tu.uname,ta.reason,ts.name,ts.true_code from t_seal_use_apply_verify t "
				+ " left join t_user tu on t.verify_user_id=tu._id "
				+ " left join t_seal_use_apply ta on t.apply_id=ta._id "
				+ " left join t_seal ts on ta.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and tu.uname like ? "
				+ " and t.note like ? and (t.verify='未通过' or t.verify='已通过') "
				+ " and t.verify_date>=? and t.verify_date<=? order by t.created desc ";

		List<String> params = new ArrayList<String>();
		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_user)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_user + "%");
		}
		if (ChkTools.isNull(srh_content)) {
			sql = sql.replace("and t.note like ?", "");
		} else {
			params.add("%" + srh_content + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.verify_date>=? and t.verify_date<=?", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	public List<Map<String, Object>> getAllUseList(String srh_reason,
			String srh_name, String srh_user, String srh_status, Page page) {
		String sql = "select tu.uname,ts.name,ts.true_code,t.* from t_seal_use_apply t  "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and t.reason like ? "
				+ " and (ts.name like ? or ts.true_code like ?) and tu.uname like ? "
				+ " and (t.pass_status like ? or t.seal_status like ? or t.verify_status like ?) "
				+ " order by t.created desc";

		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace("and (ts.name like ? or ts.true_code like ?)", "");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_user)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_user + "%");
		}
		if (ChkTools.isNull(srh_status)) {
			sql = sql.replace("and (t.pass_status like ? or t.seal_status like ? or t.verify_status like ?)", "");
		} else {
			params.add("%" + srh_status + "%");
			params.add("%" + srh_status + "%");
			params.add("%" + srh_status + "%");
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

}
